package gbench.appdemo.priceline.model;

import java.util.Date;
import java.util.List;

import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc.JdbcConfig;
import gbench.common.tree.LittleTree.Jdbc.JdbcExecute;
import gbench.common.tree.LittleTree.Jdbc.JdbcQuery;

/**
 * PriceLine 价格线数据库操作
 * @author gbench
 *
 */
@JdbcConfig(url="jdbc:mysql://localhost:3306/futures?serverTimezone=GMT%2B8")
public interface PriceLineDatabase{
    
    /**
     * 删除表数据
     */
    @JdbcExecute("drop table if exists {0} ")
    public void dropTable( final String tableName);
    
    /**
     * 创建一张价格表:
     */
    @JdbcExecute({"create table {0} ("
        + "id bigint primary key auto_increment comment ''主键'',"
        + "ticker varchar(64) comment ''合约代码'',"
        + "period bigint comment ''价格周期'',"
        + "time timestamp comment ''更新时间'',"
        + "`index` bigint comment ''kpoint索引'',"
        + "open double comment ''开盘价格'',"
        + "high double comment ''最高价格'',"
        + "low double comment ''最低价格'',"
        + "close double comment ''收盘价格'',"
        + "volume bigint comment ''成交量'',"
        + "volume0 bigint comment ''初始成交量'',"
        + "create_time timestamp default current_timestamp comment ''创建时间''" // 默认的创建是时间
        + ") ",// 表格创建
        "alter table {0} add constraint unique_{0} unique(ticker,period,`index`)", // 修改约束:ticker,period,index 是唯一key
        "alter table {0} add constraint unique_{0}2 unique(`index`)", // 修改约束:index 是唯一key
        "create index {0}_index on {0}(`index`)"
    })
    public void createTable( final String tableName );
    
    /**
     * 删除指定ticker的
     * ＠param tableName 表名
     * ＠param ticker 期货代码
     */
    @JdbcExecute("delete from  {0} where ticker=''{1}''")
    public void delete( final String tableName, final String ticker );

    /**
     * 创建索引数据索引
     */
    @JdbcExecute("create index idx_index on {0}(`index`,period) ")
    public void createIndex( final String tableName );
    
    /**
     * 检索出指定的价格线数据。
     * @param ticker 合约代码
     * @param period 时间周期
     * @return IRecord 数据记录集合
     */
    @JdbcQuery("select `index`,time,open,high,low,close,volume,close*.9 settle,close*0.8 presettle "
        + "from {0} where ticker=''{1}'' and period = {2,number,#} and `index`>0 "
        + "order by time")
    public List<IRecord> getPriceline( final String tableName, final String ticker, final long period );
    
    /**
     * 检索出指定的价格线数据。
     * 返回的数据范围：[start,start+size)
     * @param ticker 合约代码
     * @param period 时间周期
     * @param start 偏移数量
     * @param size 页面大小
     * @return IRecord 数据记录集合
     */
    @JdbcQuery("select `index`,time,open,high,low,close,volume,close*0.9 settle,close*0.8 presettle "
        + "from {0} where ticker=''{1}'' and period = {2,number,#} and `index`>0 "
        + "order by time "
        + "limit {3,number,#},{4,number,#} "
        + "")
    public List<IRecord> getPriceline( final String tableName, final String ticker, 
            final long period, final long start, final long size );
    
    /**
     * 检索指定的索引OHLC即kpoint的数据
     * 返回的数据范围：[start,start+size)
     * 
     * @param tableName 表名
     * @param ticker 合约代码
     * @param period 时间周期
     * @param index 索引
     * @return KPoint的数据
     */
    @JdbcQuery("select `index`,time,open,high,low,close,volume "
        + "from {0} where ticker=''{1}'' and period = {2,number,#} and `index`={3,number,#} "
        + "")
    public IRecord getKPoint( final String tableName, final String ticker, final long period, final long index );
    
    /**
     * 计算一个价格线条
     * @param numerator_ticker 分子的ticker
     * @param denominator_ticker 分子的ticker
     * @param period 期间
     * @param start 开始位置：首项位置
     * @param size 长度
     * @return Record的集合
     */
    @JdbcQuery(
        "select "
            + "f.`index`,f.time,f.open/if(c.open=0,1,c.open) open,"
            + "f.high/if(c.high=0,1,c.high) high,f.low/if(c.low=0,1,c.low) low,"
            + "f.close/if(c.close=0,1,c.close) close,f.volume/if(c.volume=0,1,c.volume) volume "
        + "from "
            + " (select * from t_price_line_{0} where period = {2,number,#} and `index`>0) f "
            + " inner join "
            + " (select * from t_price_line_{1} where period = {2,number,#} and `index`>0) c "
        + "on f.`index` = c.`index` and f.`period` = c.`period` "
        + "limit {3,number,#},{4,number,#}"
    )
    public List<IRecord> getPriceLine( final String numerator_ticker, final String denominator_ticker, 
            final long period, final long start, final long size );
    
    
    /**
     * 检索出一条价格数据点:价格数据点有3个座座标
     * @param ticker 期货代码
     * @param period 期货捡起
     * @param index 周期点索引。
     * @return Record记录
     */
    @JdbcQuery("select * from {0} where "
        + "ticker=''{1}'' and period = {2,number,#} and `index`={3,number,#} limit 1")
    public  IRecord findOne( final String tableName, final String ticker, final long period, final long index );
    
    /**
     * 寻找出第一条数据记录
     * @param ticker 期货代码
     * @param period 期货捡起
     * @param index 周期点索引。
     * @return 第一个条数据记录
     */
    @JdbcQuery("select * from {0} where "
        + "ticker=''{1}'' and period = {2,number,#} order by time limit 1;")
    public  IRecord findFirst( final String tableName, final String ticker, final long period );
    
    /**
     *  检查数据表是否存在
     * @param tableName 数据表
     * @return 表是否存在 true 存在,false 不存在
     */
    @JdbcQuery("show tables like ''{0}''")
    public Boolean exists( final String tableName );
    
    /**
     * 删除索引
     * @param tableName
     * @return IRecord数据记录(无业务意义)
     */
    @JdbcQuery("drop index idx_index on ''{0}''")
    public IRecord dropIndex( final String tableName );
    
    /**
     * 根据 ticker period,index 更新数据
     * @param ticker
     * @param period
     * @param index
     * @param high
     * @param low
     * @param close
     * @param volume
     */
    @JdbcExecute("insert into "
        + "{0} (ticker,period,`index`,time,open,high,low,close,volume,volume0) "
        + "values(''{1}'',{2,number,#},{3,number,#},''{4,date,yyyy-MM-dd HH:mm:ss}'',"
        + "{5,number,#},{6,number,#},{7,number,#},{8,number,#},{9,number,#},{10,number,#}) "
        // 更新数据
        + "on duplicate key update " //  唯一性约束为:ticker,period,`index`
        + "time=values(time),open=values(open),high=values(high),low=values(low),close=values(close),"
        + "volume=values(volume),volume0=values(volume0)"
    )
    public void persist(final String tableName,final String ticker,final long period,final long index,final Date time,
        final double open, final double high, final double low, final double close, final long volume,final long volume0 );
}